﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Transactions;
using System.Windows.Forms;
using System.Data.OracleClient;
namespace Project.dalSql
{
    public  class dalTransactionScope
    {
        /// <summary>
        /// 纯事务处理方法一
        /// 适合多表的插入操作
        /// 无返回值
        /// </summary>
        /// <param name="Conn"></param>
        /// <param name="Sql"></param>
        public static void ExecuteTransaction(SqlConnection Conn, List<string> Sql)
        {
            SqlTransaction Trans = Conn.BeginTransaction();
            try
            {
                Boolean[] Logic = new Boolean[Sql.Count];
                for (int i = 0; i < Sql.Count; i++)
                {
                    SqlCommand cmd = new SqlCommand(Sql[i].ToString(), Conn, Trans);
                    Logic[i] = cmd.ExecuteNonQuery() > 0;
                }
                Trans.Commit();

            }
            catch (SqlException Ex)
            {
                MessageBox.Show(Ex.Message.ToString());
                Trans.Rollback();
            }
            finally
            {
                dalSqlHelper.CloseConnObject(Conn);
            }
        }
        /// <summary>
        /// 纯事务处理方法二
        /// </summary>
        /// <param name="SqlObject"></param>
        /// <returns></returns>
        public static bool ExecuteSqlTransaction(List<SqlObject> SqlObject)
        {
                Boolean[] ExecuteResult = new Boolean[SqlObject.Count];
                SqlConnection Conn = dalSql.dalSqlHelper.GetConnObject();
                Conn.Open();
                SqlTransaction trans = Conn.BeginTransaction(); //在Conn连接上创建事务对象
                int AffectRowsCount,SqlSubFlg = 0;
                try
                {
                    for (int i = 0; i < SqlObject.Count; i++)
                    {
                        cmd = new SqlCommand(SqlObject[i].Sql.ToString(), Conn,trans);
                        foreach (SqlParameter Pram in SqlObject[i].PramS)
                        {
                            cmd.Parameters.Add(Pram);
                        }
                        SqlSubFlg = i;
                        AffectRowsCount = cmd.ExecuteNonQuery();
                        ExecuteResult[i] = AffectRowsCount >= 0;
                        cmd = null;
                        AffectRowsCount = 0;
                    }
                    trans.Commit(); //提交事务
                    return true;
                }
                catch (SqlException Ex)
                {
                    MessageBox.Show("第" + (SqlSubFlg+1) + "个SQL语句出现了异常\n" + "异常为:" + Ex.Message.ToString());
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    dalSqlHelper.CloseConnObject(Conn);
                }
        }

        /// <summary>
        /// 下面执行的是本机的分布式新增事务
        ///通过 TransactionScope事务范围类
        ///来执行 单数据库(同一个数据库) 
        ///
        /// </summary>
        /// 
        public static SqlCommand cmd = null;
        public static bool ExecuteTransactionByTransactionScopeAdd(List<SqlObject> SqlObject)
        {
            using (TransactionScope Ts = new TransactionScope()) 
            {
                Boolean[] ExecuteResult = new Boolean[SqlObject.Count];
                SqlConnection Conn = dalSql.dalSqlHelper.GetConnObject();
                Conn.Open();
                int AffectRowsCount,SqlSubFlg = 0;
                try
                {
                    for (int i = 0; i < SqlObject.Count; i++)
                    {
                        cmd = new SqlCommand(SqlObject[i].Sql.ToString(), Conn);
                        foreach (SqlParameter Pram in SqlObject[i].PramS)
                        {
                            cmd.Parameters.Add(Pram);
                        }
                        SqlSubFlg = i;
                        AffectRowsCount = cmd.ExecuteNonQuery();
                        ExecuteResult[i] = AffectRowsCount >0;
                        cmd = null;
                        AffectRowsCount = 0;
                    }
                    Ts.Complete(); //提交事务
                    return true;
                }
                catch (SqlException Ex)
                {
                    MessageBox.Show("第" + (SqlSubFlg+1) + "个SQL语句出现了异常\n" + "异常为:" + Ex.Message.ToString());
                    return false;
                }
                finally
                {
                    dalSqlHelper.CloseConnObject(Conn);
                }
                
            }
        }
        /// <summary>
        ///  下面执行的是本机的分布式修改,删除事务
        ///通过 TransactionScope事务范围类
        ///来执行 单数据库(同一个数据库)
        /// </summary>
        /// <param name="SqlObject"></param>
        /// <returns></returns>
        public static bool ExecuteTransactionByTransactionScopeUpDel(List<SqlObject> SqlObject)
        {
            using (TransactionScope Ts = new TransactionScope())
            {
                Boolean[] ExecuteResult = new Boolean[SqlObject.Count];
                SqlConnection Conn = dalSql.dalSqlHelper.GetConnObject();
                Conn.Open();
                int AffectRowsCount, SqlSubFlg = 0;
                try
                {
                    for (int i = 0; i < SqlObject.Count; i++)
                    {
                        cmd = new SqlCommand(SqlObject[i].Sql.ToString(), Conn);
                        foreach (SqlParameter Pram in SqlObject[i].PramS)
                        {
                            cmd.Parameters.Add(Pram);
                        }
                        SqlSubFlg = i;
                        AffectRowsCount = cmd.ExecuteNonQuery();
                        ExecuteResult[i] = AffectRowsCount >= 0;
                        cmd = null;
                        AffectRowsCount = 0;
                    }
                    Ts.Complete(); //提交事务
                    return true;
                }
                catch (SqlException Ex)
                {
                    MessageBox.Show("第" + (SqlSubFlg + 1) + "个SQL语句出现了异常\n" + "异常为:" + Ex.Message.ToString());
                    return false;
                }
                finally
                {
                    dalSqlHelper.CloseConnObject(Conn);
                }

            }
        }



        /// <summary>
        /// 对不同的类型的数据库执行事务操作
        /// 如Oracle,SQLServer,MySql,DB
        /// </summary>
        /// <param name="DBConnTypeC"></param>
        /// <param name="SqlStatementObject"></param>
        /// <returns></returns>
        public static bool ExectuteTransactionScopeMultiDatabase(List<Object> DBConnTypeC, List<SqlObject> SqlStatementObject
            ,List<SqlthousandObject> thousandSql)
        {
            int AffectRowsCount,SqlSubFlg = 0;
            List<Boolean> ExecuteResult = new List<Boolean>();

            int receiveCount = 0;
            TransactionOptions transactionOption = new TransactionOptions();

            //设置事务隔离级别 
            transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

            // 设置事务超时时间为60秒 
            transactionOption.Timeout = new TimeSpan(0, 0, 60);
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,transactionOption) )
            {
                try
                {
                    for (int i = 0; i < DBConnTypeC.Count; i++)
                    {
                        //Type t = DBConnTypeC[i].GetType();
                        switch (((System.Data.SqlClient.SqlConnection)(DBConnTypeC[i])).Database.ToString())
                        {
                            case "StudyTest": //sql2005
                                SqlConnection Conn = dalSqlHelper.GetConnObject();
                                Conn.Open();
                                for (int j = 0; j < SqlStatementObject.Count; j++)
                                {
                                    SqlCommand cmd = new SqlCommand(SqlStatementObject[j].Sql, Conn);
                                    foreach (SqlParameter Pram in SqlStatementObject[j].PramS)
                                    {
                                        cmd.Parameters.Add(Pram);
                                    }
                                    SqlSubFlg = i;
                                    AffectRowsCount = cmd.ExecuteNonQuery();
                                    ExecuteResult.Add(AffectRowsCount > 0);
                                    cmd = null;
                                    AffectRowsCount = 0;
                                }
                                break;
                            case "TestDB":
                                SqlConnection Conn2000 = dalSqlHelper.GetConnObjectthoursand();
                                Conn2000.Open();
                                for (int m = 0; m < thousandSql.Count; m++)
                                {
                                    SqlCommand cmd1 = new SqlCommand(thousandSql[m].Sql, Conn2000);
                                    foreach (SqlParameter Pram in thousandSql[m].PramS)
                                    {
                                        cmd1.Parameters.Add(Pram);
                                    }
                                    //SqlSubFlg = i;
                                    AffectRowsCount = cmd1.ExecuteNonQuery();
                                    ExecuteResult.Add(AffectRowsCount > 0);
                                    cmd1= null;
                                    AffectRowsCount = 0;
                                }
                                break;
                            //case "System.Data.OracleClient.OracleConnection":
                            //    OracleConnection Oracle_Conn = (OracleConnection)DBConnTypeC[i];
                            //    Oracle_Conn.Open();
                            //    for (int j = 0; j < OracelStatementObject.Count; j++)
                            //    {
                            //        OracleCommand Oracle_cmd = new OracleCommand(OracelStatementObject[j].Sql, Oracle_Conn);
                            //        foreach (OracleParameter Pram in OracelStatementObject[j].PramS)
                            //        {
                            //            Oracle_cmd.Parameters.Add(Pram);
                            //        }
                            //        SqlSubFlg = i;
                            //        AffectRowsCount = Oracle_cmd.ExecuteNonQuery();
                            //        ExecuteResult.Add(AffectRowsCount > 0);
                            //        Oracle_cmd = null;
                            //        AffectRowsCount = 0;
                            //    }
                            //    break;

                            default:
                                break;
                        }
                    }
                    ts.Complete();
                    return true;
                }
                catch (SqlException SqlEx)
                {
                    MessageBox.Show(SqlEx.Message.ToString());
                    return false;
                }
                catch (Exception OracleEx)
                {
                    MessageBox.Show(OracleEx.Message.ToString());
                    return false;
                }
                finally
                {
                    for (int i = 0; i < DBConnTypeC.Count; i++)
                    {
                        Type t = DBConnTypeC[i].GetType();
                        switch (t.ToString())
                        {
                            case "System.Data.SqlClient.SqlConnection":
                                dalSqlHelper.CloseConnObject((SqlConnection)DBConnTypeC[i]);
                                break;
                            case "System.Data.OracleClient.OracleConnection":
                                dalSqlHelper.CloseOracleConnObject((OracleConnection)DBConnTypeC[i]);
                                break;
                            default:
                                break;
                        }
                    }
                }
            
            }
        }
       
    }
}
